package mainpackage;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import org.omg.CORBA.PUBLIC_MEMBER;

public class DataManager {

	/**
	 * to manage colors not yet taken by categories <Color enum, isVacant>
	 */
	private Map<Color, Boolean> vacantColors = new HashMap<Color, Boolean>();

	/**
	 * SQL statement object for query execution
	 */
	private Statement statement;

	private List<Todo> todos = new ArrayList<Todo>();
	private List<Category> categories = new ArrayList<Category>();

	/**
	 * @param dbName
	 *            for example sample.db
	 */
	public DataManager(String dbName) {
		// add all colors as vacant:
		for (Color color : Color.values()) {
			vacantColors.put(color, true);
		}
		// get data from database:
		pullData(dbName);
		// mark used colors:
		for (Category category : categories) {
			vacantColors.put(category.getColor(), false);
		}
	}

	public void addTodo(Todo todo) {
		String sql = "insert into Todo values(" + "'" + todo.getId() + "',"
				+ "'" + todo.getName() + "'," + "'" + todo.getDescription()
				+ "'," + "'" + convertDate(todo.getStartTime()) + "'," + "'"
				+ convertDate(todo.getEndTime()) + "'," + "'"
				+ todo.getCategory().getName() + "')";
		try {
			statement.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		todos.add(todo);// category existence is not checked, since later user
						// will choose it, not write
	}

	public void removeTodo(Todo todo) {
		String sql = "DELETE FROM Todo WHERE id='" + todo.getId() + "'";
		try {
			statement.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		todos.remove(todo);
	}

	/**
	 * @param todo
	 *            initial todo
	 * @param name
	 *            modified field
	 * @param description
	 *            modified field
	 * @param startTime
	 *            modified field
	 * @param endTime
	 *            modified field
	 * @param category
	 *            modified category
	 * @throws EndDateIsBeforeStartException
	 * @throws ParseException
	 *             wrong date format
	 */
	public void editTodo(Todo todo, String name, String description,
			String startTime, String endTime, Category category)
			throws EndDateIsBeforeStartException, ParseException {
		// category existence is not checked, since later user
		// will choose it, not write
		todo.editData(name, description, startTime, endTime, category);

		String sql = "UPDATE Todo SET name='" + name + "', description='"
				+ description + "', start='" + startTime + "', end='" + endTime
				+ "', category_name='" + category.getName() + "' WHERE id="
				+ todo.getId();
		try {
			statement.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * @param name
	 * @return newly created category
	 * @throws NoColorsLeftException
	 * @throws CategoryNameNotUniqueException
	 */
	public Category addCategory(String name) throws NoColorsLeftException,
			CategoryNameNotUniqueException {
		if (!categoryNameIsUnique(name)) {
			throw new CategoryNameNotUniqueException();
		}
		Category category;
		Color color = getVacantColor();
		category = new Category(name, color);

		String sql = "insert into Category values(" + "'" + category.getName()
				+ "', " + "'" + category.getColor() + "');";
		try {
			statement.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		categories.add(category);
		// would remove old value:
		vacantColors.put(color, false);
		return category;
	}

	/**
	 * @param name
	 * @return
	 * @throws SQLException
	 */
	public boolean removeCategory(String name) throws SQLException {
		for (Category c : categories) {
			if (c.getName().equals(name)) {

				String sql = "DELETE FROM Category WHERE name='" + name + "'";
				statement.executeUpdate(sql);
				categories.remove(c);
				vacantColors.put(c.getColor(), true);
				return true;
			}
		}
		return false;
	}

	// shows todos from all categories
	public Todo getTodoById(int id) {
		for (Todo todo : todos) {
			if (todo.getId() == id) {
				return todo;
			}
		}
		return null;
	}

	// shows todos from all categories
	public List<Todo> getTodo(Date start, Date end) {
		List<Todo> result = new ArrayList<Todo>();
		for (Todo todo : todos) {
			if (todoIsBetween(start, end, todo)) {
				result.add(todo);
			}
		}
		return result;
	}

	// shows todos from specified categories
	public List<Todo> getTodo(Date start, Date end,
			List<String> neededCategories) {
		List<Todo> result = new ArrayList<Todo>();
		List<Todo> filteredByCategory = getAllTodos(neededCategories);
		for (Todo todo : filteredByCategory) {
			if (todoIsBetween(start, end, todo)) {
				result.add(todo);
			}
		}
		return result;

	}

	// shows todos from all categories
	public List<Todo> getAllTodos() {
		List<Todo> result = new ArrayList<Todo>();
		for (Todo todo : todos) {
			result.add(todo);
		}

		return result;
	}

	// shows todos from specified categories
	public List<Todo> getAllTodos(List<String> neededCategories) {
		// TODO category is not checked
		List<Todo> result = new ArrayList<Todo>();
		for (Todo todo : todos) {
			Category category = todo.getCategory();
			if (neededCategories.contains(category.getName())) {
				result.add(todo);
			}
		}

		return result;
	}

	/**
	 * @param name
	 * @return Category if found, null otherwise
	 */
	public Category getCategoryByName(String name) {
		for (Category c : categories) {
			if (c.getName().equals(name)) {
				return c;
			}
		}
		return null;
	}
	
	public List<Category> getCategories() {
		return categories;
	}
	
	public boolean hasVacantColor() {
		for (Color color : vacantColors.keySet()) {
			if (vacantColors.get(color) == true) {
				return true;
			}
		}
		return false;
	}

	/**
	 * For getting initial data
	 * 
	 * @param dbName
	 */
	public void pullData(String dbName) {
		try {
			Class.forName("org.sqlite.JDBC");
		} catch (ClassNotFoundException e1) {
		}

		Connection connection = null;
		try {
			// create a database connection
			connection = DriverManager.getConnection("jdbc:sqlite:" + dbName);
			statement = connection.createStatement();
			statement.setQueryTimeout(30); // set timeout to 30 sec.

			statement.executeUpdate("PRAGMA foreign_keys = ON");

			// add categories
			ResultSet rs = statement.executeQuery("select * from Category");
			while (rs.next()) {
				String categoryName = rs.getString("name");
				String categoryColor = rs.getString("color");
				Category newCategory = new Category(categoryName,
						Color.valueOf(categoryColor));
				categories.add(newCategory);
			}

			// add todos
			rs = statement.executeQuery("select * from Todo");
			while (rs.next()) {
				String name = rs.getString("name");
				String desc = rs.getString("description");
				String start = rs.getString("start");
				String end = rs.getString("end");
				String categoryName = rs.getString("category_name");
				Category category = getCategoryByName(categoryName);
				Todo newTodo = null;
				try {
					newTodo = new Todo(name, desc, start, end, category);
					todos.add(newTodo);
				} catch (EndDateIsBeforeStartException e) {
					e.printStackTrace();
				} catch (ParseException e) {
					e.printStackTrace();
				}
			}
			setLastTodoId();

		} catch (SQLException e) {
			// if the error message is "out of memory",
			// it probably means no database file is found
			System.err.println(e.getMessage());
		} /*
		 * finally { try { if (connection != null) connection.close(); } catch
		 * (SQLException e) { // connection close failed. System.err.println(e);
		 * } }
		 */// TODO close connection?
	}

	/**
	 * since unique id's are created by java, we need to set highest id
	 */
	private void setLastTodoId() {
		int lastTodoId = 0;
		for (Todo todo : todos) {
			if (todo.getId() > lastTodoId) {
				lastTodoId = todo.getId();
			}
		}
		Todo.setLastId(lastTodoId);
	}

	/**
	 * @param date
	 * @return date converted to string which will be understood by sqLite
	 */
	private String convertDate(Date date) {
		SimpleDateFormat dateFormat = new SimpleDateFormat("yy/MM/dd H:m");
		String result = dateFormat.format(date);
		return result;
	}

	private Color getVacantColor() throws NoColorsLeftException {
		for (Color color : vacantColors.keySet()) {
			if (vacantColors.get(color) == true) {
				return color;
			}
		}
		// if there are no vacant colors:
		throw new NoColorsLeftException();
	}

	/**
	 * Checks if Todo is at least partially located between specified dates
	 * 
	 * @param start
	 * @param end
	 * @param todo
	 * @return true if it is
	 */
	private boolean todoIsBetween(Date start, Date end, Todo todo) {
		Date todoStart;
		Date todoEnd;
		todoStart = todo.getStartTime();
		todoEnd = todo.getEndTime();
		if (dateBetween(todoStart, start, end)
				|| dateBetween(todoEnd, start, end)) {
			return true;
		} else {
			return false;
		}
	}

	private boolean dateBetween(Date todoStart, Date start, Date end) {
		if (todoStart.after(start) && todoStart.before(end)) {
			return true;
		} else if (todoStart.equals(start) || todoStart.equals(end))
			return true;
		else
			return false;
	}

	/**
	 * checks if category with specified name exists
	 * 
	 * @param name
	 * @return
	 */
	private boolean categoryNameIsUnique(String name) {
		for (Category c : categories) {
			if (c.getName().equals(name)) {
				return false;
			}
		}
		return true;
	}

	// validate todos
	public boolean timeIsFree(Date start, Date end) {
		for (Todo todo : todos) {
			if (todoIsBetween(start, end, todo)) {
				return false;
			}
		}
		return true;
	}

}
